/**********************************************************************************************************************
 * SplendidCRM is a Customer Relationship Management program created by SplendidCRM Software, Inc. 
 * Copyright (C) 2005-2011 SplendidCRM Software, Inc. All rights reserved.
 * 
 * This program is free software: you can redistribute it and/or modify it under the terms of the 
 * GNU Affero General Public License as published by the Free Software Foundation, either version 3 
 * of the License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; 
 * without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. 
 * See the GNU Affero General Public License for more details.
 * 
 * You should have received a copy of the GNU Affero General Public License along with this program. 
 * If not, see <http://www.gnu.org/licenses/>. 
 * 
 * You can contact SplendidCRM Software, Inc. at email address support@splendidcrm.com. 
 * 
 * In accordance with Section 7(b) of the GNU Affero General Public License version 3, 
 * the Appropriate Legal Notices must display the following words on all interactive user interfaces: 
 * "Copyright (C) 2005-2011 SplendidCRM Software, Inc. All rights reserved."
 *********************************************************************************************************************/
using System;
using System.IO;
using System.Data;
using System.Data.Common;
using System.Collections;
using System.Collections.Specialized;
using System.Drawing;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Diagnostics;
using System.Xml;
using System.Text;

namespace SplendidCRM.Import
{
	public partial class ImportView : SplendidControl
    {
        #region Designer
        protected _controls.ModuleHeader ctlModuleHeader;
        protected _controls.DynamicButtons ctlDynamicButtons;
        protected _controls.ListHeader ctlListHeader;
        //protected _controls.Chooser        ctlDuplicateFilterChooser;
        protected _controls.Chooser ctlOverWriteColumnChooser;
        protected HiddenField ctlOverWriteColumnChooserHidden;

        protected _controls.Chooser ctlKeyColumnChooser;
        protected HiddenField ctlKeyColumnChooserHidden;
        protected SplendidControl ctlDefaultsView;

        protected Guid gID;


        protected DataView vwMain;
        protected DataView vwColumns;
        protected DataView vwMySaved;

        protected XmlDocument xml;
        protected XmlDocument xmlMapping;
        protected string sImportModule;
        protected StringBuilder sbImport;


        protected int nMAX_ERRORS = 500;

        #endregion

        #region Properties & Get value methods
        public string Module
        {
            get { return sImportModule; }
            set { sImportModule = value; }
        }
        public DataView ColumnList
        {
            get
            {
                var columnList = new DataView(SplendidCache.ImportColumns(sImportModule).Copy());
                foreach (DataRow row in columnList.Table.Rows)
                {
                    row["DISPLAY_NAME"] = TableColumnName(sImportModule, Sql.ToString(row["DISPLAY_NAME"]));
                }
                return columnList;
            }
        }
        protected string SourceType()
        {
            return "excel";
        }
        protected void SourceType(string sSource)
        {
            switch (sSource)
            {
                case "excel":
                    radEXCEL.Checked = true;
                    break;
            }
        }
        private string TableColumnName(string sModule, string sDISPLAY_NAME)
        {
            return Utils.TableColumnName(L10n, sModule, sDISPLAY_NAME);
        }
        #endregion

        #region Methods

        #region Load & get Chooser
        //Load from hidden field selected values & add to listbox
        private void LoadChooserFilterList()
        {
            var overWriteLeftList = ctlOverWriteColumnChooser.LeftListBox;
            var overWriteRightList = ctlOverWriteColumnChooser.RightListBox;
            var keyLeftList = ctlKeyColumnChooser.LeftListBox;
            var keyRightList = ctlKeyColumnChooser.RightListBox;
            //Load OverWriteColumnChooser
            if (ctlOverWriteColumnChooser.RightValuesTable == null) {
                overWriteRightList.DataValueField = "NAME"; overWriteRightList.DataTextField = "DISPLAY_NAME";
                overWriteRightList.DataSource = ColumnList; overWriteRightList.DataBind();
            } else {
                AddColumnNameFromTableToList(ctlOverWriteColumnChooser.RightValuesTable, overWriteRightList);
                AddColumnNameFromTableToList(ctlOverWriteColumnChooser.LeftValuesTable, overWriteLeftList);
            }
            //Load KeyColumnChooser
            if (ctlKeyColumnChooser.RightValuesTable == null) {
                keyRightList.DataValueField = "NAME"; keyRightList.DataTextField = "DISPLAY_NAME";
                keyRightList.DataSource = ColumnList; keyRightList.DataBind();
            } else {
                AddColumnNameFromTableToList(ctlKeyColumnChooser.RightValuesTable, keyRightList);
                AddColumnNameFromTableToList(ctlKeyColumnChooser.LeftValuesTable, keyLeftList);
            }
        }
        private static void AddColumnNameFromTableToList(DataTable table, ListBox listBox)
        {
            listBox.Items.Clear();
            foreach (DataRow row in table.Rows) {
                listBox.Items.Add(new ListItem(row["text"].ToString(), row["value"].ToString()));
            }
        }
        #endregion

        #region Process Mapping
        private void InitMapping()
        {
            // 10/18/2006 Paul.  Initalize the fields. 
            XmlUtil.RemoveAllChildren(xmlMapping, "Fields");
            XmlNode xFields = xmlMapping.DocumentElement.SelectSingleNode("Fields");
            if (xFields == null)
            {
                xFields = xmlMapping.CreateElement("Fields");
                xmlMapping.DocumentElement.AppendChild(xFields);
            }

            vwColumns.Sort = "colid";
            foreach (DataRowView row in vwColumns)
            {
                XmlNode xField = xmlMapping.CreateElement("Field");
                xFields.AppendChild(xField);

                string sColumnName = Sql.ToString(row["Name"]);
                XmlUtil.SetSingleNodeAttribute(xmlMapping, xField, "Name", sColumnName);
                XmlUtil.SetSingleNode(xmlMapping, xField, "Type", Sql.ToString(row["ColumnType"]));
                XmlUtil.SetSingleNode(xmlMapping, xField, "Length", Sql.ToString(row["Size"]));
                XmlUtil.SetSingleNode(xmlMapping, xField, "Default", String.Empty);
                XmlUtil.SetSingleNode(xmlMapping, xField, "Mapping", String.Empty);
                XmlUtil.SetSingleNode(xmlMapping, xField, "DuplicateFilter", "false");
            }
            // 12/17/2008 Paul.  Display the available columns. 
            LoadChooserFilterList();
        }
        //Dipslay & Update columns mapping
        protected void UpdateImportMappings(XmlDocument xml, bool bInitialize, bool bUpdateMappings)
        {
			var hashFieldMappings = new Hashtable();
			tblImportMappings.Rows.Clear();
			var rowHeader = new HtmlTableRow();     tblImportMappings.Rows.Add(rowHeader);
			var cellField  = new HtmlTableCell();   var cellRowHdr = new HtmlTableCell();
            var cellRow1 = new HtmlTableCell();     var cellRow2 = new HtmlTableCell();
			rowHeader.Cells.Add(cellField);

			if ( chkHasHeader.Checked || radXML.Checked )
				rowHeader.Cells.Add(cellRowHdr);

			rowHeader.Cells.Add(cellRow1  ); rowHeader.Cells.Add(cellRow2  );
			cellField .Attributes.Add("class", "tabDetailViewDL");      cellRowHdr.Attributes.Add("class", "tabDetailViewDL");
			cellRow1  .Attributes.Add("class", "tabDetailViewDL");      cellRow2  .Attributes.Add("class", "tabDetailViewDL");
			cellField .Attributes.Add("style", "TEXT-ALIGN: left");     cellRowHdr.Attributes.Add("style", "TEXT-ALIGN: left");
            cellRow2  .Attributes.Add("style", "TEXT-ALIGN: left");
			var lblField  = new Label(); var lblRowHdr = new Label();
			var lblRow1   = new Label(); var lblRow2   = new Label();

			cellField .Controls.Add(lblField ); cellRowHdr.Controls.Add(lblRowHdr);
			cellRow1  .Controls.Add(lblRow1  ); cellRow2  .Controls.Add(lblRow2  );
			lblField .Font.Bold = true; lblRowHdr.Font.Bold = true;
			lblRow1  .Font.Bold = true; lblRow2  .Font.Bold = true;
			lblField .Text = L10n.Term("Import.LBL_DATABASE_FIELD");        lblRowHdr.Text = L10n.Term("Import.LBL_HEADER_ROW"    );
			lblRow1  .Text = L10n.Term("Import.LBL_ROW"           ) + " 1"; lblRow2  .Text = L10n.Term("Import.LBL_ROW"           ) + " 2";

            if (xml.DocumentElement == null) return;

            var nl = xml.DocumentElement.SelectNodes(sImportModule.ToLower());
            if (nl.Count <= 0) return;

            vwColumns.Sort = "DISPLAY_NAME";
            XmlNode nodeH = nl[0]; XmlNode node1 = nl[0]; XmlNode node2 = null;

            if ( chkHasHeader.Checked ) {
                if ( nl.Count > 1 ) node1 = nl[1];
                if ( nl.Count > 2 ) node2 = nl[2];
            } else {
                if ( nl.Count > 1 ) node2 = nl[1];
            }
            var hashSelectedFields = new Hashtable();
            for ( var i = 0 ; i < nodeH.ChildNodes.Count ; i++ )
            {
                rowHeader = new HtmlTableRow();
                tblImportMappings.Rows.Add(rowHeader);
                cellField  = new HtmlTableCell(); cellRowHdr = new HtmlTableCell();
                cellRow1   = new HtmlTableCell(); cellRow2   = new HtmlTableCell();
                rowHeader.Cells.Add(cellField );
                if ( chkHasHeader.Checked || radXML.Checked ) rowHeader.Cells.Add(cellRowHdr);
                rowHeader.Cells.Add(cellRow1  );
                if ( node2 != null && i < node2.ChildNodes.Count ) rowHeader.Cells.Add(cellRow2);
                cellField .Attributes.Add("class", "tabDetailViewDF"); cellRowHdr.Attributes.Add("class", "tabDetailViewDF");
                cellRow1  .Attributes.Add("class", "tabDetailViewDF"); cellRow2  .Attributes.Add("class", "tabDetailViewDF");
                DropDownList lstField  = new KeySortDropDownList();
                lblRowHdr = new Label(); lblRow1   = new Label(); lblRow2   = new Label();
                cellField .Controls.Add(lstField ); cellRowHdr.Controls.Add(lblRowHdr);
                cellRow1  .Controls.Add(lblRow1  ); cellRow2  .Controls.Add(lblRow2  );
						

                vwColumns.RowFilter = null;

                lstField.ID             = "ImportField" + i.ToString("000");
                lstField.DataValueField = "NAME";       lstField.DataTextField  = "DISPLAY_NAME";
                lstField.DataSource     = vwColumns;    lstField.DataBind();
						
                lstField.Items.Insert(0, new ListItem(L10n.Term("Import.LBL_DONT_MAP"), String.Empty));
                try
                {
                    if ( bInitialize )
                    {
                        if ( chkHasHeader.Checked )
                        {
                            var sFieldName = nodeH.ChildNodes[i].InnerText.Trim();
                            vwColumns.RowFilter = "NAME = '" + sFieldName.Replace("'", "''") + "' or DISPLAY_NAME = '" + sFieldName.Replace("'", "''") + "' or NAME = '" + sFieldName.Replace("'", "''").Replace(" ", "_") + "_C" + "'";
                            if ( vwColumns.Count > 0 )
                            {
                                hashFieldMappings.Add(i, Sql.ToString(vwColumns[0]["NAME"]));
                                Utils.SetSelectedValue(lstField, Sql.ToString(vwColumns[0]["NAME"]));
                            }
                        } else if ( radXML.Checked ) {
                            var sFieldName = nodeH.ChildNodes[i].Name;
                            vwColumns.RowFilter = "NAME = '" + sFieldName.Replace("'", "''") + "' or DISPLAY_NAME = '" + sFieldName.Replace("'", "''") + "' or NAME = '" + sFieldName.Replace("'", "''").Replace(" ", "_") + "_C" + "'";
                            if ( vwColumns.Count > 0 ) {
                                hashFieldMappings.Add(i, Sql.ToString(vwColumns[0]["NAME"]));
                                Utils.SetSelectedValue(lstField, Sql.ToString(vwColumns[0]["NAME"]));
                            }
                        } else {
                            hashFieldMappings.Add(i, "ImportField" + i.ToString("000"));
                        }   
                    } else {
                        hashFieldMappings.Add(i, Sql.ToString(Request[lstField.UniqueID]));
                        Utils.SetSelectedValue(lstField, Sql.ToString(Request[lstField.UniqueID]));
                        if ( lstField.SelectedValue.Length > 0 ) {
                            if ( !hashSelectedFields.ContainsKey(lstField.SelectedValue) ) {
                                hashSelectedFields.Add(lstField.SelectedValue, null);
                            }
                        }
                    }
                }
                catch
                {
                }
                // XML data will use the node-name as the header. 
                if ( chkHasHeader.Checked )
                {
                    // 08/22/2006 Paul.  If Has Header is checked, then always expect the body to contain the header names. 
                    lblRowHdr.Text = nodeH.ChildNodes[i].InnerText;
                }
                else if ( radXML.Checked )
                {
                    // 08/22/2006 Paul.  If Has Header is not checked for XML, then use the tag name as the field name. 
                    lblRowHdr.Text = nodeH.ChildNodes[i].Name;
                }
						
                if ( node1 != null && i < node1.ChildNodes.Count )
                    lblRow1.Text = node1.ChildNodes[i].InnerText;
                if ( node2 != null && i < node2.ChildNodes.Count )
                    lblRow2.Text = node2.ChildNodes[i].InnerText;
            }
					
            if ( bUpdateMappings )
            {
                var nlFields = xmlMapping.DocumentElement.SelectNodes("Fields/Field");
                foreach ( XmlNode xField in nlFields )
                {
                    XmlUtil.SetSingleNode(xmlMapping, xField, "Mapping", String.Empty);
                }
                // 08/22/2006 Paul.  We should always use the header mappings instead of an index as nodes may move around. 
                var node = nl[0];
                for ( int j = 0; j < node.ChildNodes.Count; j++ )
                {
                    var xField = xmlMapping.DocumentElement.SelectSingleNode("Fields/Field[@Name='" + hashFieldMappings[j] + "']");
                    if ( xField != null )
                    {
                        XmlUtil.SetSingleNode(xmlMapping, xField, "Mapping", node.ChildNodes[j].Name);
                    }
                }
            }
            else
            {
                // 12/17/2008 Paul.  Apply current filter fields. 
                LoadChooserFilterList();
                // 10/12/2006 Paul.  If we are not updating the mappings, then we are setting the mappings. 
                var nlFields = xmlMapping.DocumentElement.SelectNodes("Fields/Field");
                foreach ( XmlNode xField in nlFields )
                {
                    string sName    = xField.Attributes.GetNamedItem("Name").Value;
                    string sMapping = XmlUtil.SelectSingleNode(xField, "Mapping");
                    if ( !Sql.IsEmptyString(sMapping) )
                    {
                        var lstField = tblImportMappings.FindControl(sMapping) as DropDownList;
                        if ( lstField != null )
                        {
                            try
                            {
                                Utils.SetSelectedValue(lstField, sName);
                            } catch {}
                        }
                    }
                }
            }
        }
        protected void ValidateMappings()
        {
            switch (sImportModule)
            {
                case "Contacts":
                    {
                        var sMapping = XmlUtil.SelectSingleNode(xmlMapping.DocumentElement, "Fields/Field[@Name='LAST_NAME']/Mapping");
                        if (Sql.IsEmptyString(sMapping))
                            throw (new Exception(L10n.Term(".ERR_MISSING_REQUIRED_FIELDS") + " " + L10n.Term("Contacts.LBL_LIST_LAST_NAME")));
                        break;
                    }
            }
        }
        #endregion

        #region Process Import
        protected void GenerateImport(string sTempFileName, bool bPreview)
        {
            try
            {
                //Get KeyColumns & OverWriteColumns ===========================================
                var dtChoosedKeyColumns = ctlKeyColumnChooser.LeftValuesTable;
                var dtOverWriteColumns = ctlOverWriteColumnChooser.LeftValuesTable;

                var keyColums = new ListItemCollection();
                if (dtChoosedKeyColumns != null)
                {
                    foreach (DataRow row in dtChoosedKeyColumns.Rows)
                    {
                        keyColums.Add(new ListItem(row["value"].ToString()));
                    }
                }
                var overWriteColumns = new ListItemCollection();
                if (dtOverWriteColumns != null)
                {
                    foreach (DataRow row in dtOverWriteColumns.Rows)
                    {
                        overWriteColumns.Add(new ListItem(row["value"].ToString()));
                    }
                }

                //Prepare connection & table name for conflict finder
                var tableName = Sql.ToString(Application["Modules." + sImportModule + ".TableName"]);
                if (Sql.IsEmptyString(tableName))
                    tableName = sImportModule.ToUpper();
                var selectSql = "select *  from vw" + tableName + " where 1 = 1 ";
                var dbf = DbProviderFactories.GetFactory();
                var connection = dbf.CreateConnection();

                //=============================================================================

                var xmlImport = new XmlDocument();
                xmlImport.Load(Path.Combine(Path.GetTempPath(), sTempFileName));

                var nlRows = xmlImport.DocumentElement.SelectNodes(sImportModule.ToLower());
                if (nlRows.Count == 0)
                    throw (new Exception(L10n.Term("Import.LBL_NOTHING")));

                var hashHeaderMappings = new StringDictionary();
                var hashReverseMappings = new StringDictionary();
                var hashDefaultMappings = new Hashtable();

                XmlNodeList nlFields = xmlMapping.DocumentElement.SelectNodes("Fields/Field");
                foreach (XmlNode xField in nlFields)
                {
                    string sName = xField.Attributes.GetNamedItem("Name").Value;
                    string sMapping = XmlUtil.SelectSingleNode(xField, "Mapping");
                    string sDefault = XmlUtil.SelectSingleNode(xField, "Default");
                    if (!Sql.IsEmptyString(sMapping))
                    {
                        if (!hashHeaderMappings.ContainsKey(sMapping))
                            hashHeaderMappings.Add(sMapping, sName);
                        if (!hashReverseMappings.ContainsKey(sName))
                            hashReverseMappings.Add(sName, sMapping);
                    }
                    if (!Sql.IsEmptyString(sDefault))
                    {
                        if (!hashDefaultMappings.ContainsKey(sName))
                            hashDefaultMappings.Add(sName, sDefault);
                    }
                }

                // 11/01/2006 Paul.  Use a hash for quick access to required fields. 
                Hashtable hashColumns = new Hashtable();
                foreach (DataRowView row in vwColumns)
                {
                    // 11/02/2009 Rick.  We need to protect against duplicate dictionary entries. 
                    if (!hashColumns.ContainsKey(row["NAME"]))
                        hashColumns.Add(row["NAME"], row["DISPLAY_NAME"]);
                }

                Hashtable hashRequiredFields = new Hashtable();
                DataTable dtRequiredFields = SplendidCache.EditViewFields(sImportModule + ".EditView");
                DataView dvRequiredFields = new DataView(dtRequiredFields);
                dvRequiredFields.RowFilter = "UI_REQUIRED = 1";
                foreach (DataRowView row in dvRequiredFields)
                {
                    string sDATA_FIELD = Sql.ToString(row["DATA_FIELD"]);
                    if (!Sql.IsEmptyString(sDATA_FIELD))
                    {
                        if (!hashRequiredFields.ContainsKey(sDATA_FIELD))
                            hashRequiredFields.Add(sDATA_FIELD, null);
                    }
                }

                int nImported = 0;
                int nFailed = 0;
                int nDuplicates = 0;
                //int nSkipped  = 0;
                DataTable dtProcessed = new DataTable();
                dtProcessed.Columns.Add("IMPORT_ROW_STATUS", typeof(bool));
                dtProcessed.Columns.Add("IMPORT_ROW_NUMBER", typeof(Int32));
                dtProcessed.Columns.Add("IMPORT_ROW_ERROR");
                dtProcessed.Columns.Add("IMPORT_LAST_COLUMN");
                dtProcessed.Columns.Add("ID");  // 10/10/2006 Paul.  Every record will have an ID, either implied or specified. 
                //Add column IsConflicted to filter only conflicted row
                dtProcessed.Columns.Add("IsConflicted", typeof(string));

                SplendidError.SystemWarning(new StackTrace(true).GetFrame(0), "Import Database Table: " + sImportModule);
                using (IDbConnection con = dbf.CreateConnection())
                {
                    con.Open();
                    // 11/01/2006 Paul.  The transaction is optional, just make sure to always dispose it. 
                    //using ( IDbTransaction trn = Sql.BeginTransaction(con) )
                    {
                        IDbTransaction trn = null;
                        try
                        {
                            var sTABLE_NAME = Sql.ToString(Application["Modules." + sImportModule + ".TableName"]);
                            if (Sql.IsEmptyString(sTABLE_NAME))
                                sTABLE_NAME = sImportModule.ToUpper();


                            IDbCommand cmdImportCSTM = null;
                            vwColumns.RowFilter = "CustomField = 1";
                            if (vwColumns.Count > 0)
                            {
                                vwColumns.Sort = "colid";
                                cmdImportCSTM = con.CreateCommand();
                                cmdImportCSTM.CommandType = CommandType.Text;
                                cmdImportCSTM.CommandText = "update " + sTABLE_NAME + "_CSTM" + ControlChars.CrLf;
                                int nFieldIndex = 0;
                                foreach (DataRowView row in vwColumns)
                                {
                                    string sNAME = Sql.ToString(row["ColumnName"]).ToUpper();
                                    string sCsType = Sql.ToString(row["ColumnType"]);
                                    // String or binary data would be truncated. The statement has been terminated. 
                                    int nMAX_SIZE = Sql.ToInteger(row["Size"]);
                                    if (nFieldIndex == 0)
                                        cmdImportCSTM.CommandText += "   set ";
                                    else
                                        cmdImportCSTM.CommandText += "     , ";
                                    cmdImportCSTM.CommandText += sNAME + " = @" + sNAME + ControlChars.CrLf;

                                    IDbDataParameter par = null;
                                    switch (sCsType)
                                    {
                                        // 09/19/2007 Paul.  In order to leverage the existing AddParameter functions, we need to provide default values. 
                                        case "Guid": par = Sql.AddParameter(cmdImportCSTM, "@" + sNAME, Guid.Empty); break;
                                        case "short": par = Sql.AddParameter(cmdImportCSTM, "@" + sNAME, 0); break;
                                        case "Int32": par = Sql.AddParameter(cmdImportCSTM, "@" + sNAME, 0); break;
                                        case "Int64": par = Sql.AddParameter(cmdImportCSTM, "@" + sNAME, 0); break;
                                        case "float": par = Sql.AddParameter(cmdImportCSTM, "@" + sNAME, 0.0f); break;
                                        case "decimal": par = Sql.AddParameter(cmdImportCSTM, "@" + sNAME, new Decimal()); break;
                                        case "bool": par = Sql.AddParameter(cmdImportCSTM, "@" + sNAME, false); break;
                                        case "DateTime": par = Sql.AddParameter(cmdImportCSTM, "@" + sNAME, DateTime.MinValue); break;
                                        default: par = Sql.AddParameter(cmdImportCSTM, "@" + sNAME, String.Empty, nMAX_SIZE); break;
                                    }
                                    nFieldIndex++;
                                }
                                // 09/19/2007 Paul.  Exclude ID_C as it is expect and required. We don't want it to appear in the mapping table. 
                                cmdImportCSTM.CommandText += " where ID_C = @ID_C" + ControlChars.CrLf;
                                Sql.AddParameter(cmdImportCSTM, "@ID_C", Guid.Empty);
                            }
                            vwColumns.RowFilter = "";

                            IDbCommand cmdImport;
                            try
                            {
                                cmdImport = SqlProcs.Factory(con, "sp" + sTABLE_NAME + "_Import");
                            }
                            catch
                            {
                                cmdImport = SqlProcs.Factory(con, "sp" + sTABLE_NAME + "_Update");
                            }


                            //==========Khoa - Create new row in IMPORTS table for this import
                            //Khoa - Get file path to insert binary-----------------
                            var now = DateTime.Now;
                            var fileExtension = ".xls";
                            if (sTempFileName.LastIndexOf(".") > -1)
                            {
                                fileExtension = sTempFileName.Substring(sTempFileName.LastIndexOf("."),
                                                                        sTempFileName.Length -
                                                                        sTempFileName.LastIndexOf("."));
                            }
                            var importFileName = "IMPORT_" + now.Year + "_" + now.Month + "_" + now.Day + fileExtension;
                            var importFilePath = Path.Combine(Path.GetTempPath(), sTempFileName);
                            //------------------------------------------------------
                            var importID = Guid.NewGuid();
                            IDbCommand cmdInsertImports = con.CreateCommand();
                            cmdInsertImports.CommandType = CommandType.Text;
                            cmdInsertImports.CommandText =
@" INSERT INTO dbo.IMPORTS( IMPORT_ID ,IMPORT_TIME ,IMPORT_DATA ,IMPORT_DATATYPE ,IMPORT_FILENAME)
 VALUES  ( @IMPORT_ID ,GETDATE() ,@IMPORT_DATA ,'XLS' ,@IMPORT_FILENAME)";
                            Sql.AddParameter(cmdInsertImports, "@IMPORT_ID", importID);
                            Sql.AddParameter(cmdInsertImports, "@IMPORT_FILENAME",
                                             Session["ImportedFileName"] == null
                                                 ? importFileName
                                                 : Session["ImportedFileName"].ToString());
                            if (Session["ImportedFileData"] == null)
                                Sql.AddParameter(cmdInsertImports, "@IMPORT_DATA", GetByteArrayFromFile(importFilePath));
                            else
                                Sql.AddParameter(cmdInsertImports, "@IMPORT_DATA", Session["ImportedFileData"] as byte[]);
                            IDbCommand cmdInsertHistory = con.CreateCommand();
                            cmdInsertHistory.CommandType = CommandType.Text;
                            cmdInsertHistory.CommandText =
@" INSERT INTO dbo." + tableName + @"_IMPORT_HISTORY( ID ,IMPORT_ID ,TARGET_ID ,CREATED_NEW)
 VALUES  ( NEWID() ,@IMPORT_ID , @TARGET_ID, @CREATED_NEW)";
                            //================================================================

                            if (chkUseTransaction.Checked || bPreview)
                            {
                                trn = Sql.BeginTransaction(con);
                                cmdImport.Transaction = trn;
                                cmdInsertImports.Transaction = trn;
                                cmdInsertHistory.Transaction = trn;
                                if (cmdImportCSTM != null)
                                    cmdImportCSTM.Transaction = trn;
                            }
                            //Exec insert import first
                            cmdInsertImports.ExecuteNonQuery();


 


                            int i = 0;
                            if (chkHasHeader.Checked)
                                i++;

                            var hashProspectLists = new Hashtable();

                            for (int iRowNumber = 1; i < nlRows.Count; i++)
                            {
                                XmlNode node = nlRows[i];
                                int nEmptyColumns = 0;
                                for (int j = 0; j < node.ChildNodes.Count; j++)
                                {
                                    string sText = node.ChildNodes[j].InnerText;
                                    if (sText == String.Empty)
                                        nEmptyColumns++;
                                }
                                // 09/04/2006 Paul.  If all columns are empty, then skip the row. 
                                if (nEmptyColumns == node.ChildNodes.Count)
                                    continue;
                                DataRow row = dtProcessed.NewRow();
                                row["IMPORT_ROW_NUMBER"] = iRowNumber;
                                iRowNumber++;
                                dtProcessed.Rows.Add(row);

                                try
                                {
                                    if (!Response.IsClientConnected)
                                    {
                                        break;
                                    }
                                    foreach (IDbDataParameter par in cmdImport.Parameters)
                                    {
                                        string sParameterName = Sql.ExtractDbName(cmdImport, par.ParameterName).ToUpper();
                                        if (sParameterName == "ASSIGNED_USER_ID")
                                            par.Value = Sql.ToDBGuid(Security.USER_ID);
                                        else
                                            par.Value = DBNull.Value;
                                    }
                                    if (cmdImportCSTM != null)
                                    {
                                        foreach (IDbDataParameter par in cmdImportCSTM.Parameters)
                                        {
                                            par.Value = DBNull.Value;
                                        }
                                    }

                                    // 09/19/2007 Paul.  parID and parID_C are frequently used, so obtain outside the import loop. 
                                    IDbDataParameter parID = Sql.FindParameter(cmdImport, "ID");
                                    IDbDataParameter parID_C = null;
                                    if (cmdImportCSTM != null)
                                        parID_C = Sql.FindParameter(cmdImportCSTM, "ID_C");

                                    Sql.SetParameter(cmdImport, "@MODIFIED_USER_ID", Security.USER_ID);
                                    foreach (string sName in hashDefaultMappings.Keys)
                                    {
                                        string sDefault = Sql.ToString(hashDefaultMappings[sName]);
                                        if (!dtProcessed.Columns.Contains(sName))
                                        {
                                            dtProcessed.Columns.Add(sName);
                                        }
                                        row["IMPORT_ROW_STATUS"] = true;
                                        row["IMPORT_LAST_COLUMN"] = sName;
                                        row[sName] = sDefault;
                                        Sql.SetParameter(cmdImport, sName, sDefault);
                                        if (cmdImportCSTM != null)
                                            Sql.SetParameter(cmdImportCSTM, sName, sDefault);
                                    }
                                    for (int j = 0; j < node.ChildNodes.Count; j++)
                                    {
                                        string sText = node.ChildNodes[j].InnerText;
                                        string sName = String.Empty;
                                        // 08/22/2006 Paul.  We should always use the header mappings instead of an index as nodes may move around. 
                                        sName = Sql.ToString(hashHeaderMappings[node.ChildNodes[j].Name]);
                                        // 09/08/2006 Paul.  There is no need to set the field if the value is empty. 
                                        if (sName.Length > 0 && sText.Length > 0)
                                        {
                                            sName = sName.ToUpper();
                                            if (sName == "ID" || sName.EndsWith("_ID") || sName == "CREATED_BY")
                                            {
                                                // 09/30/2006 Paul.  IDs must be in upper case.  This is primarily for platforms that are case-significant. 
                                                // 10/05/2006 Paul.  We need to use upper case for SQL Server as well so that the SugarCRM user names are correctly replaced. 
                                                sText = sText.ToUpper();
                                                if (sText.Length < 36 && sText.Length > 0)
                                                {
                                                    sText = "00000000-0000-0000-0000-000000000000".Substring(0, 36 - sText.Length) + sText;
                                                    switch (sText)
                                                    {
                                                        case "00000000-0000-0000-0000-000000JIM_ID": sText = "00000000-0000-0000-0001-000000000000"; break;
                                                        case "00000000-0000-0000-0000-000000MAX_ID": sText = "00000000-0000-0000-0002-000000000000"; break;
                                                        case "00000000-0000-0000-0000-00000WILL_ID": sText = "00000000-0000-0000-0003-000000000000"; break;
                                                        case "00000000-0000-0000-0000-0000CHRIS_ID": sText = "00000000-0000-0000-0004-000000000000"; break;
                                                        case "00000000-0000-0000-0000-0000SALLY_ID": sText = "00000000-0000-0000-0005-000000000000"; break;
                                                        case "00000000-0000-0000-0000-0000SARAH_ID": sText = "00000000-0000-0000-0006-000000000000"; break;
                                                        // 11/30/2006 Paul.  The following mappings will really only help when importing SugarCRM sample data. 
                                                        case "00000000-0000-0000-0000-000000000001": sText = "00000000-0000-0001-0000-000000000000"; break;
                                                        case "00000000-0000-0000-0000-0PRIVATE.JIM": sText = "00000000-0000-0001-0001-000000000000"; break;
                                                        case "00000000-0000-0000-0000-0PRIVATE.MAX": sText = "00000000-0000-0001-0002-000000000000"; break;
                                                        case "00000000-0000-0000-0000-PRIVATE.WILL": sText = "00000000-0000-0001-0003-000000000000"; break;
                                                        case "00000000-0000-0000-0000PRIVATE.CHRIS": sText = "00000000-0000-0001-0004-000000000000"; break;
                                                        case "00000000-0000-0000-0000PRIVATE.SALLY": sText = "00000000-0000-0001-0005-000000000000"; break;
                                                        case "00000000-0000-0000-0000PRIVATE.SARAH": sText = "00000000-0000-0001-0006-000000000000"; break;
                                                        case "00000000-0000-0000-0000-00000000EAST": sText = "00000000-0000-0001-0101-000000000000"; break;
                                                        case "00000000-0000-0000-0000-00000000WEST": sText = "00000000-0000-0001-0102-000000000000"; break;
                                                        case "00000000-0000-0000-0000-0000000NORTH": sText = "00000000-0000-0001-0103-000000000000"; break;
                                                        case "00000000-0000-0000-0000-0000000SOUTH": sText = "00000000-0000-0001-0104-000000000000"; break;
                                                        // 07/09/2010 Paul.  New IDs used in a prepopulated SugarCRM database. 
                                                        case "00000000-0000-0000-0000-0SEED_JIM_ID": sText = "00000000-0000-0000-0011-000000000000"; break;
                                                        case "00000000-0000-0000-0000-0SEED_MAX_ID": sText = "00000000-0000-0000-0012-000000000000"; break;
                                                        case "00000000-0000-0000-0000-SEED_WILL_ID": sText = "00000000-0000-0000-0013-000000000000"; break;
                                                        case "00000000-0000-0000-0000SEED_CHRIS_ID": sText = "00000000-0000-0000-0014-000000000000"; break;
                                                        case "00000000-0000-0000-0000SEED_SALLY_ID": sText = "00000000-0000-0000-0015-000000000000"; break;
                                                        case "00000000-0000-0000-0000SEED_SARAH_ID": sText = "00000000-0000-0000-0016-000000000000"; break;
                                                    }
                                                }
                                            }
                                            if (!dtProcessed.Columns.Contains(sName))
                                            {
                                                dtProcessed.Columns.Add(sName);
                                            }
                                            row["IMPORT_ROW_STATUS"] = true;
                                            row["IMPORT_LAST_COLUMN"] = sName;
                                            row[sName] = sText;
                                            Sql.SetParameter(cmdImport, sName, sText);
                                            if (cmdImportCSTM != null)
                                                Sql.SetParameter(cmdImportCSTM, sName, sText);
                                        }
                                    }
                                    StringBuilder sbRequiredFieldErrors = new StringBuilder();
                                    foreach (string sRequiredField in hashRequiredFields.Keys)
                                    {
                                        IDbDataParameter par = Sql.FindParameter(cmdImport, sRequiredField);
                                        if (par == null && cmdImportCSTM != null)
                                            par = Sql.FindParameter(cmdImportCSTM, sRequiredField);
                                        if (par != null)
                                        {
                                            if (par.Value == DBNull.Value || par.Value.ToString() == String.Empty)
                                            {

                                                // 11/02/2006 Paul.  If ACCOUNT_ID is required, then also allow ACCOUNT_NAME. 
                                                if (sRequiredField == "ACCOUNT_ID" && (sImportModule == "Cases " || sImportModule == "Opportunities"))
                                                {
                                                    par = Sql.FindParameter(cmdImport, "ACCOUNT_NAME");
                                                    if (par != null)
                                                    {
                                                        if (par.Value != DBNull.Value && par.Value.ToString() != String.Empty)
                                                        {
                                                            continue;
                                                        }
                                                    }
                                                }
                                                if (sbRequiredFieldErrors.Length > 0)
                                                    sbRequiredFieldErrors.Append(", ");
                                                if (hashColumns.ContainsKey(sRequiredField))
                                                    sbRequiredFieldErrors.Append(hashColumns[sRequiredField]);
                                                else
                                                    sbRequiredFieldErrors.Append(sRequiredField);
                                            }
                                        }
                                    }

                                    if (sbRequiredFieldErrors.Length > 0)
                                    {
                                        row["IMPORT_ROW_STATUS"] = false;
                                        row["IMPORT_ROW_ERROR"] = L10n.Term("Import.ERR_MISSING_REQUIRED_FIELDS") + " " + sbRequiredFieldErrors.ToString();
                                        nFailed++;
                                        // 10/31/2006 Paul.  Abort after 200 errors. 
                                        if (nFailed >= nMAX_ERRORS)
                                        {
                                            ctlDynamicButtons.ErrorText += L10n.Term("Import.LBL_MAX_ERRORS");
                                            break;
                                        }
                                    }
                                    else
                                    {
                                        sbImport.Append(Sql.ExpandParameters(cmdImport));
                                        sbImport.AppendLine(";");

                                        //BEGIN Preview conflict values=======================================
                                        if (connection.State != ConnectionState.Open)
                                            connection.Open();
                                        row["IsConflicted"] = "0";

                                        var cmdSelect = connection.CreateCommand();
                                        var searchString = selectSql;
                                        foreach (ListItem keyColumn in keyColums)
                                        {
                                            if (row[keyColumn.Text.ToUpper()] == DBNull.Value)
                                                searchString += " and " + keyColumn.Text.ToUpper() + " is null ";
                                            else
                                            {
                                                searchString += " and " + keyColumn.Text.ToUpper() + " = @" + keyColumn.Text.ToUpper();
                                                var param = Sql.CreateParameter(cmdSelect, "@" + keyColumn.Text.ToUpper());
                                                param.Value = row[keyColumn.Text.ToUpper()];
                                            }
                                        }
                                        cmdSelect.CommandText = searchString;
                                        cmdSelect.CommandType = CommandType.Text;

                                        var reader = cmdSelect.ExecuteReader();
                                        var existedCount = 0; var existedID = Guid.Empty;
                                        while (reader.Read())
                                        {
                                            existedCount++;
                                            for (var j = 0; j < reader.FieldCount; j++)
                                            {
                                                var currentCol = reader.GetName(j).ToUpper();

                                                if (row.Table.Columns.IndexOf(currentCol) > -1 && currentCol != "ID")
                                                {
                                                    if (Sql.ToString(row[currentCol]) != Sql.ToString(reader[currentCol]))
                                                    {
                                                        row[reader.GetName(j).ToUpper()] =
                                                        "<span style='color:red'>"
                                                        + Sql.ToString(row[currentCol]) +
                                                        "</span><br/>"
                                                        + Sql.ToString(reader[currentCol]);
                                                        row["IsConflicted"] = "1";
                                                    }
                                                } else if (currentCol == "ID")
                                                {
                                                    try {
                                                        existedID = new Guid(reader["ID"].ToString());
                                                    }
                                                    catch (Exception){}
                                                }
                                            }
                                        }
                                        reader.Close();
                                        if (existedCount == 0) row["IsConflicted"] = "-1";
                                        //END Preview conflict values===============================================
                                        if (row["IsConflicted"].ToString() == "0") continue;
                                        if (existedID != Guid.Empty) {
                                            var p = Sql.FindParameter(cmdImport, "@ID"); p.Value = existedID;
                                        }
                                            
                                        cmdImport.ExecuteNonQuery();
                                        if (parID != null)
                                        {
                                            row["ID"] = parID.Value;

                                            Guid gID = Sql.ToGuid(parID.Value);
                                            if (cmdImportCSTM != null && parID_C != null)
                                            {
                                                parID_C.Value = gID;
                                                sbImport.Append(Sql.ExpandParameters(cmdImportCSTM));
                                                sbImport.AppendLine(";");
                                                cmdImportCSTM.ExecuteNonQuery();
                                            }
                                        }
                                        if (row["IsConflicted"].ToString() == "-1") nImported++;
                                        else if (row["IsConflicted"].ToString() == "1") nDuplicates++;
                                        row["IMPORT_LAST_COLUMN"] = DBNull.Value;

                                        //===========INSERT MODULE IMPORT HISTORY====
                                        cmdInsertHistory.Parameters.Clear();
                                        Sql.AddParameter(cmdInsertHistory, "@IMPORT_ID", importID);
                                        Sql.AddParameter(cmdInsertHistory, "@TARGET_ID", Sql.ToGuid(parID.Value));
                                        Sql.AddParameter(cmdInsertHistory, "@CREATED_NEW", (existedCount <= 0));
                                        cmdInsertHistory.ExecuteNonQuery();
                                        //===========================================
                                    }
                                    Response.Write(" ");
                                }
                                catch (Exception ex)
                                {
                                    row["IMPORT_ROW_STATUS"] = false;
                                    row["IMPORT_ROW_ERROR"] = L10n.Term("Import.LBL_ERROR") + " " + Sql.ToString(row["IMPORT_LAST_COLUMN"]) + ". " + ex.Message;
                                    nFailed++;
                                    // 10/31/2006 Paul.  Abort after 200 errors. 
                                    if (nFailed >= nMAX_ERRORS)
                                    {
                                        ctlDynamicButtons.ErrorText += L10n.Term("Import.LBL_MAX_ERRORS");
                                        break;
                                    }
                                }
                            }
                            // 10/29/2006 Paul.  Save the processed table so that the result can be browsed. 
                            string sProcessedFileID = Guid.NewGuid().ToString();
                            string sProcessedFileName = Security.USER_ID.ToString() + " " + Guid.NewGuid().ToString() + ".xml";
                            DataSet dsProcessed = new DataSet();
                            dsProcessed.Tables.Add(dtProcessed);
                            dsProcessed.WriteXml(Path.Combine(Path.GetTempPath(), sProcessedFileName), XmlWriteMode.WriteSchema);
                            Session["TempFile." + sProcessedFileID] = sProcessedFileName;
                            ViewState["ProcessedFileID"] = sProcessedFileID;

                            //==========Khoa - update import status to IMPORTS table
                            IDbCommand cmdUpdateImportStatus = con.CreateCommand();
                            cmdUpdateImportStatus.CommandType = CommandType.Text;
                            cmdUpdateImportStatus.CommandText =
@" UPDATE dbo.IMPORTS
 SET TOTAL_ROWS = @TOTAL_ROWS, TOTAL_SUCCESS_ROWS = @TOTAL_SUCCESS_ROWS, TOTAL_DUPLICATE_ROWS = @TOTAL_DUPLICATE_ROWS 
 WHERE IMPORT_ID = @IMPORT_ID";
                            Sql.AddParameter(cmdUpdateImportStatus, "@TOTAL_ROWS", nlRows.Count);
                            Sql.AddParameter(cmdUpdateImportStatus, "@TOTAL_SUCCESS_ROWS", nImported);
                            Sql.AddParameter(cmdUpdateImportStatus, "@TOTAL_DUPLICATE_ROWS", nDuplicates);
                            Sql.AddParameter(cmdUpdateImportStatus, "@IMPORT_ID", importID);
                            if (chkUseTransaction.Checked || bPreview)
                            {
                                cmdUpdateImportStatus.Transaction = trn;
                            }
                            cmdUpdateImportStatus.ExecuteNonQuery();
                            //======================================================

                            if (trn != null && !bPreview)
                            {
                                trn.Commit();
                            }
                        }
                        catch (Exception ex)
                        {
                            if (trn.Connection != null)
                                trn.Rollback();
                            nFailed++;
                            ctlDynamicButtons.ErrorText += ex.Message;
                        }
                        finally
                        {
                            if (trn != null)
                                trn.Dispose();
                        }
                    }
                }
                lblStatus.Text = String.Empty;
                // 03/20/2011 Paul.  Include a preview indicator. 
                if (bPreview)
                    lblStatus.Text += L10n.Term("Import.LBL_PREVIEW_BUTTON_LABEL") + " ";
                if (nFailed == 0)
                    lblStatus.Text += L10n.Term("Import.LBL_SUCCESS");
                else
                    lblStatus.Text += L10n.Term("Import.LBL_FAIL");
                lblSuccessCount.Text = nImported.ToString() + " " + L10n.Term("Import.LBL_SUCCESSFULLY");
                lblFailedCount.Text = nFailed.ToString() + " " + L10n.Term("Import.LBL_FAILED_IMPORT");
                lblDuplicateCount.Text = nDuplicates.ToString() + " " + L10n.Term("Import.LBL_DUPLICATES_IGNORED");

                grdMain.SortColumn = "IMPORT_ROW_STATUS, IMPORT_ROW_NUMBER";
                grdMain.SortOrder = "asc";

                PreviewGrid(dtProcessed);
            }
            catch (Exception ex)
            {
                ctlDynamicButtons.ErrorText += ex.Message;
            }
        }

        /// <summary>
        /// Get byte array from file to insert into database
        /// </summary>
        /// <param name="importFilePath"></param>
        /// <returns></returns>
        public byte[] GetByteArrayFromFile(string importFilePath)
        {
            byte[] buffer = null;

            try
            {
                // Open file for reading
                var fileStream = new FileStream(importFilePath, FileMode.Open, FileAccess.Read);
                // attach filestream to binary reader
                var binaryReader = new BinaryReader(fileStream);
                // get total byte length of the file
                var totalBytes = new FileInfo(importFilePath).Length;
                // read entire file into buffer
                buffer = binaryReader.ReadBytes((Int32)totalBytes);
                // close file reader
                fileStream.Close();
                fileStream.Dispose();
                binaryReader.Close();
            }
            catch (Exception ex)
            {
                ctlDynamicButtons.ErrorText += ex.Message;
            }
            return buffer;
        }

	    private void PreviewGrid(DataTable dtProcessed)
        {
            /*
            //BEGIN Preview conflict values
            var dbf = DbProviderFactories.GetFactory();
            var tableName = Sql.ToString(Application["Modules." + sImportModule + ".TableName"]);
            if (Sql.IsEmptyString(tableName))
                tableName = sImportModule.ToUpper();
            var selectSql = "select *  from vw" + tableName + " where 1 = 1 ";
            var connection = dbf.CreateConnection();
            connection.Open();

            var dtChoosedKeyColumns = ctlKeyColumnChooser.LeftValuesTable;
		    var dtOverWriteColumns = ctlOverWriteColumnChooser.LeftValuesTable;

            //Get KeyColumns & OverWriteColumns
		    var keyColums = new ListItemCollection();
            if (dtChoosedKeyColumns != null)
            {
                foreach (DataRow row in dtChoosedKeyColumns.Rows)
                {
                    keyColums.Add(new ListItem(row["value"].ToString()));
                }
            }
            var overWriteColumns = new ListItemCollection();
            if (dtOverWriteColumns != null)
            {
                foreach (DataRow row in dtOverWriteColumns.Rows)
                {
                    overWriteColumns.Add(new ListItem(row["value"].ToString()));
                }
            }

            //Add column IsConflicted to filter only conflicted row
		    dtProcessed.Columns.Add("IsConflicted", typeof (string));

            foreach (DataRow row in dtProcessed.Rows)
            {
                //Default IsConflicted = false
                row["IsConflicted"] = "0";
                
                var cmdSelect = connection.CreateCommand();
                var searchString = selectSql;
                foreach (ListItem keyColumn in keyColums)
                {
                    if (row[keyColumn.Text.ToUpper()] == DBNull.Value)
                        searchString += " and " + keyColumn.Text.ToUpper() + " is null ";
                    else
                    {
                        searchString += " and " + keyColumn.Text.ToUpper() + " = @" + keyColumn.Text.ToUpper();
                        var param = Sql.CreateParameter(cmdSelect, "@" + keyColumn.Text.ToUpper());
                        param.Value = row[keyColumn.Text.ToUpper()];
                    }
                }
                cmdSelect.CommandText = searchString;
                cmdSelect.CommandType = CommandType.Text;
                var reader = cmdSelect.ExecuteReader();

                while (reader.Read())
                {
                    for (var j = 0; j < reader.FieldCount; j++)
                    {
                        var currentCol = reader.GetName(j).ToUpper();

                        if (row.Table.Columns.IndexOf(currentCol) > -1 && currentCol != "ID")
                        {
                            if (Sql.ToString(row[currentCol]) != Sql.ToString(reader[currentCol]))
                            {
                                row[reader.GetName(j).ToUpper()] =
                                "<span style='color:red'>"
                                + Sql.ToString(row[currentCol]) +
                                "</span><br/>"
                                + Sql.ToString(reader[currentCol]);
                                row["IsConflicted"] = "1";
                            }
                        }
                    }
                }
                reader.Close();
            }
            connection.Close();
            //END Preview conflict values
            */
            //Remove none conflict row
            var iRemoveRow = 0;
            while (iRemoveRow < dtProcessed.Rows.Count)
            {
                if (dtProcessed.Rows[iRemoveRow]["IsConflicted"].ToString() != "1")
                {
                    dtProcessed.Rows.RemoveAt(iRemoveRow);
                }
                else
                {
                    iRemoveRow++;
                }
            }
            dtProcessed.Columns.Remove("IsConflicted");
            dtProcessed.Columns.Remove("IMPORT_ROW_STATUS");
            dtProcessed.Columns.Remove("IMPORT_ROW_ERROR");

            vwColumns.Sort = "DISPLAY_NAME";
            var hashColumns = new Hashtable();
            foreach (DataRowView row in vwColumns)
                hashColumns.Add(row["NAME"], row["DISPLAY_NAME"]);

            // 10/31/2006 Paul.  Always reset columns before adding them. 
            grdMain.Columns.Clear();

            BoundColumn bnd = new BoundColumn();
            bnd.DataField = "IMPORT_ROW_NUMBER";
            bnd.SortExpression = bnd.DataField;
            bnd.HeaderText = L10n.Term("Import.LBL_ROW");
            grdMain.Columns.Add(bnd);
            /*
			bnd = new BoundColumn();
			bnd.DataField  = "IMPORT_ROW_ERROR";
			bnd.SortExpression = bnd.DataField;
			bnd.HeaderText = L10n.Term("Import.LBL_ROW_STATUS");
			grdMain.Columns.Add(bnd);
            */
            for (int i = 4; i < dtProcessed.Columns.Count; i++)
            {
                bnd = new BoundColumn();
                bnd.DataField = dtProcessed.Columns[i].ColumnName;
                bnd.SortExpression = bnd.DataField;
                if (hashColumns.ContainsKey(bnd.DataField))
                    bnd.HeaderText = hashColumns[bnd.DataField] as string;
                else
                    bnd.HeaderText = bnd.DataField;
                grdMain.Columns.Add(bnd);
            }

            foreach (DataGridColumn col in grdMain.Columns)
            {
                if (col.HeaderText.Equals("ID") || col.HeaderText.Equals("Assigned User ID"))
                    col.Visible = false;
            }

            grdMain.DataSource = new DataView(dtProcessed);
            grdMain.ApplySort();
            grdMain.DataBind();
        }
        #endregion

        private void BindSaved()
        {
            /*
            DbProviderFactory dbf = DbProviderFactories.GetFactory();
            using (IDbConnection con = dbf.CreateConnection())
            {
                string sSQL;
                sSQL = "select *                                   " + ControlChars.CrLf
                     + "  from vwIMPORT_MAPS_List                  " + ControlChars.CrLf
                     + " where MODULE           = @MODULE          " + ControlChars.CrLf
                     + "   and ASSIGNED_USER_ID = @ASSIGNED_USER_ID" + ControlChars.CrLf
                     + " order by NAME                             " + ControlChars.CrLf;
                using (IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = sSQL;
                    Sql.AddParameter(cmd, "@MODULE", sImportModule);
                    Sql.AddParameter(cmd, "@ASSIGNED_USER_ID", Security.USER_ID);

                    if (bDebug)
                        RegisterClientScriptBlock("vwIMPORT_MAPS_List", Sql.ClientScriptBlock(cmd));

                    using (DbDataAdapter da = dbf.CreateDataAdapter())
                    {
                        ((IDbDataAdapter)da).SelectCommand = cmd;
                        using (DataTable dt = new DataTable())
                        {
                            da.Fill(dt);
                            vwMySaved = new DataView(dt);
                            grdMySaved.DataSource = vwMySaved;
                            grdMySaved.DataBind();
                        }
                    }
                }
            }*/
        }

        #endregion

        #region Event handlers
        protected void Page_Command(Object sender, CommandEventArgs e)
        {
            try
            {
                if (e.CommandName == "Import.Run" || e.CommandName == "Import.Preview")
                {
                    if (Page.IsValid)
                    {
                        var sTempFileId = Sql.ToString(ViewState["TempFileID"]);
                        var sTempFileName = Sql.ToString(Session["TempFile." + sTempFileId]);
                        if (Sql.IsEmptyString(sTempFileId) || Sql.IsEmptyString(sTempFileName)) {
                            txtACTIVE_TAB.Value = "3";
                            throw (new Exception(L10n.Term("Import.LBL_NOTHING")));
                        }
                        txtACTIVE_TAB.Value = "4";
                        ValidateMappings();
                        txtACTIVE_TAB.Value = "5";
                        //An ACT! import can take a long time. 
                        Server.ScriptTimeout = 20 * 60;
                        //Log the import time. 
                        SplendidError.SystemWarning(new StackTrace(true).GetFrame(0), "Begin Import");
                        GenerateImport(sTempFileName, e.CommandName == "Import.Preview");
                        SplendidError.SystemWarning(new StackTrace(true).GetFrame(0), "End Import");
                    }
                }
                else if (e.CommandName == "Import.Upload")
                {
                    reqFILENAME.Enabled = true;
                    reqFILENAME.Validate();
                    if (Page.IsValid)
                    {
                        var pstImport = fileIMPORT.PostedFile;
                        if (pstImport != null)
                        {
                            if (pstImport.FileName.Length > 0)
                            {
                                var sFilename = Path.GetFileName(pstImport.FileName);
                                var sFileExt = Path.GetExtension(sFilename);
                                var sFileMimeType = pstImport.ContentType;

                                //==================Khoa - get imported file binary
                                var binReader = new BinaryReader(fileIMPORT.PostedFile.InputStream);
                                Session["ImportedFileName"] = fileIMPORT.PostedFile.FileName;
                                Session["ImportedFileData"] = binReader.ReadBytes((int)fileIMPORT.PostedFile.InputStream.Length);
                                fileIMPORT.PostedFile.InputStream.Position = 0;
                                //=================================================

                                SplendidError.SystemWarning(new StackTrace(true).GetFrame(0), "Begin Upload: " + sFilename);
                                //We need to be able to distinguish between Excel 2003 and Excel 2007. 
                                xml = SplendidImport.ConvertStreamToXml(sImportModule, SourceType(), txtCUSTOM_DELIMITER_VAL.Text, pstImport.InputStream, sFileExt);



                                //No file exception
                                if (xml.DocumentElement == null) throw (new Exception(L10n.Term("Import.LBL_NOTHING")));

                                //No data exception
                                XmlNodeList nlRows = xml.DocumentElement.SelectNodes(sImportModule.ToLower());
                                if (nlRows.Count == 0) throw (new Exception(L10n.Term("Import.LBL_NOTHING")));

                                var sTempFileID = Guid.NewGuid().ToString();
                                var sTempFileName = Security.USER_ID.ToString() + " " + Guid.NewGuid().ToString() + " " + sFilename + ".xml";
                                xml.Save(Path.Combine(Path.GetTempPath(), sTempFileName));
                                // 01/30/2010 Paul.  Were were not storing the full path in the Session for cleanup. 
                                Session["TempFile." + sTempFileID] = Path.Combine(Path.GetTempPath(), sTempFileName);
                                ViewState["TempFileID"] = sTempFileID;

                                var xmlSample = new XmlDocument();
                                xmlSample.AppendChild(xmlSample.CreateProcessingInstruction("xml", "version=\"1.0\" encoding=\"UTF-8\""));
                                xmlSample.AppendChild(xmlSample.CreateElement("xml"));
                                for (var i = 0; i < nlRows.Count && i < 3; i++)
                                {
                                    var node = nlRows[i];
                                    xmlSample.DocumentElement.AppendChild(xmlSample.ImportNode(node, true));
                                }
                                xml = xmlSample; nlRows = null;
                                GC.Collect();
                                Session[sImportModule + ".xmlSample." + sFilename] = xml.OuterXml;
                                ViewState["xmlSample"] = sImportModule + ".xmlSample." + sFilename;

                                var bUpdateMapping = (Request["ID"] == null);
                                UpdateImportMappings(xml, bUpdateMapping, bUpdateMapping);
                                txtACTIVE_TAB.Value = "4";
                                SplendidError.SystemWarning(new StackTrace(true).GetFrame(0), "End Upload: " + sFilename);
                            }
                        }
                    }
                    if (xml.DocumentElement == null)
                        throw (new Exception(L10n.Term("Import.LBL_NOTHING")));
                }
                else if (e.CommandName == "Cancel")
                {
                    string sRelativePath = Sql.ToString(Application["Modules." + sImportModule + ".RelativePath"]);
                    if (Sql.IsEmptyString(sRelativePath))
                        sRelativePath = "~/" + sImportModule + "/";
                    Response.Redirect(sRelativePath);
                }
            }
            catch (Exception ex)
            {
                ctlDynamicButtons.ErrorText += ex.Message;
                return;
            }
        }
        protected void Page_Load(object sender, System.EventArgs e)
        {
            SetPageTitle(L10n.Term("Import.LBL_MODULE_NAME"));
            Visible = Security.IS_ADMIN || (Security.GetUserAccess(sImportModule, "import") >= 0);
            if (!Visible) {
                Parent.DataBind(); return;
            }

            xml = new XmlDocument(); xmlMapping = new XmlDocument();
            xmlMapping.AppendChild(xmlMapping.CreateProcessingInstruction("xml", "version=\"1.0\" encoding=\"UTF-8\""));
            xmlMapping.AppendChild(xmlMapping.CreateElement("Import"));

            sbImport = new StringBuilder();
            try
            {
                reqNAME.DataBind(); reqFILENAME.DataBind();
                // Don't buffer so that the connection can be kept alive. 
                Response.BufferOutput = false;
                BindSaved();
                // Columns table is used in multiple locations.  Make sure to load only once. 
                var dtColumns = SplendidCache.ImportColumns(sImportModule).Copy();
                foreach (DataRow row in dtColumns.Rows) {
                    row["DISPLAY_NAME"] = TableColumnName(sImportModule, Sql.ToString(row["DISPLAY_NAME"]));
                }
                vwColumns = new DataView(dtColumns);

                gID = Sql.ToGuid(Request["ID"]);
                if (!IsPostBack)
                {
                    radEXCEL.Checked = true;
                    chkHasHeader.Checked = true;
                    txtACTIVE_TAB.Value = "1";

                    radEXCEL.DataBind();
                    /*radXML_SPREADSHEET.DataBind(); radXML.DataBind(); radSALESFORCE.DataBind(); radACT_2005.DataBind();
                    radDBASE.DataBind(); radCUSTOM_CSV.DataBind(); radCUSTOM_TAB.DataBind(); radCUSTOM_DELIMITED.DataBind();*/

                    radEXCEL.Attributes.Add("onclick", "SelectSourceFormat()");
                    /*radXML_SPREADSHEET.Attributes.Add("onclick", "SelectSourceFormat()");
                    radXML.Attributes.Add("onclick", "SelectSourceFormat()");
                    radSALESFORCE.Attributes.Add("onclick", "SelectSourceFormat()");
                    radACT_2005.Attributes.Add("onclick", "SelectSourceFormat()");
                    radDBASE.Attributes.Add("onclick", "SelectSourceFormat()");
                    radCUSTOM_CSV.Attributes.Add("onclick", "SelectSourceFormat()");
                    radCUSTOM_TAB.Attributes.Add("onclick", "SelectSourceFormat()");
                    radCUSTOM_DELIMITED.Attributes.Add("onclick", "SelectSourceFormat()");*/
                    ctlListHeader.Title = L10n.Term("Import.LBL_LAST_IMPORTED") + " " + L10n.Term(".moduleList.", sImportModule);

                    
                    if (!Sql.IsEmptyGuid(gID))
                    {
                        var dbf = DbProviderFactories.GetFactory();
                        using (var con = dbf.CreateConnection())
                        {
                            string sSQL = "select *                 " + ControlChars.CrLf
                                          + "  from vwIMPORT_MAPS_Edit" + ControlChars.CrLf
                                          + " where ID = @ID          " + ControlChars.CrLf;
                            using (var cmd = con.CreateCommand())
                            {
                                cmd.CommandText = sSQL; Sql.AddParameter(cmd, "@ID", gID);
                                con.Open();

                                if (bDebug) RegisterClientScriptBlock("vwIMPORT_MAPS_Edit", Sql.ClientScriptBlock(cmd));

                                using (var rdr = cmd.ExecuteReader(CommandBehavior.SingleRow))
                                {
                                    if (rdr.Read())
                                    {
                                        ViewState["ID"] = gID;
                                        SourceType(Sql.ToString(rdr["SOURCE"]));
                                        //Update the name of the loaded import. 
                                        txtNAME.Text = Sql.ToString(rdr["NAME"]);
                                        chkHasHeader.Checked = Sql.ToBoolean(rdr["HAS_HEADER"]);

                                        var sXmlMapping = Sql.ToString(rdr["CONTENT"]);
                                        ViewState["xmlMapping"] = sXmlMapping;
                                        xmlMapping.LoadXml(sXmlMapping);

                                        // Update the loaded default values. 
                                        var nlFields = xmlMapping.DocumentElement.SelectNodes("Fields/Field");
                                        foreach (XmlNode xField in nlFields)
                                        {
                                            var sFieldName = xField.Attributes.GetNamedItem("Name").Value;
                                            var ctl = new DynamicControl(ctlDefaultsView, sFieldName);
                                            if (ctl.Exists) {
                                                var sDefaultValue = XmlUtil.SelectSingleNode(xField, "Default");
                                                ctl.Text = sDefaultValue;
                                            }
                                        }

                                        //Extract the sample from the mapping. 
                                        xml = new XmlDocument();
                                        string sXmlSample = XmlUtil.SelectSingleNode(xmlMapping, "Sample");
                                        // Store the sample data in the Session to prevent a huge download. 
                                        // We are seeing a 13M html file for an 8M import file. 
                                        Session[sImportModule + ".xmlSample." + gID.ToString()] = sXmlSample;
                                        ViewState["xmlSample"] = sImportModule + ".xmlSample." + gID.ToString();
                                        XmlUtil.SetSingleNode(xmlMapping, "Sample", String.Empty);
                                        if (sXmlSample.Length > 0)
                                            xml.LoadXml(sXmlSample);

                                        UpdateImportMappings(xml, false, false);
                                        txtACTIVE_TAB.Value = "3";
                                    }
                                }
                            }
                        }
                    }
                    else
                    {
                        XmlUtil.SetSingleNodeAttribute(xmlMapping, xmlMapping.DocumentElement, "Name", String.Empty);
                        XmlUtil.SetSingleNode(xmlMapping, "Module", sImportModule);
                        XmlUtil.SetSingleNode(xmlMapping, "SourceType", SourceType());
                        XmlUtil.SetSingleNode(xmlMapping, "HasHeader", chkHasHeader.Checked.ToString());
                        InitMapping();
                    }
                }
                else
                {
                    string sXmlMapping = Sql.ToString(ViewState["xmlMapping"]);
                    if (sXmlMapping.Length > 0)
                        xmlMapping.LoadXml(sXmlMapping);

                    XmlUtil.SetSingleNodeAttribute(xmlMapping, xmlMapping.DocumentElement, "Name", txtNAME.Text);
                    XmlUtil.SetSingleNode(xmlMapping, "Module", sImportModule);
                    XmlUtil.SetSingleNode(xmlMapping, "SourceType", SourceType());
                    XmlUtil.SetSingleNode(xmlMapping, "HasHeader", chkHasHeader.Checked.ToString());
                    // Update the duplicate filter and then rebind. 
                    LoadChooserFilterList();

                    // This loop updates the default values. Field mappings are updated inside UpdateImportMappings(). 
                    var nlFields = xmlMapping.DocumentElement.SelectNodes("Fields/Field");
                    foreach (XmlNode xField in nlFields)
                    {
                        var sFieldName = xField.Attributes.GetNamedItem("Name").Value;
                        var ctl = new DynamicControl(ctlDefaultsView, sFieldName);
                        if (ctl.Exists) {
                            XmlUtil.SetSingleNode(xmlMapping, xField, "Default", ctl.Text);
                        }
                    }

                    string sXmlSample = Sql.ToString(Session[Sql.ToString(ViewState["xmlSample"])]);
                    if (sXmlSample.Length > 0)
                    {
                        xml.LoadXml(sXmlSample);
                        UpdateImportMappings(xml, false, true);
                    }

                    string sProcessedFileID = Sql.ToString(ViewState["ProcessedFileID"]);
                    string sProcessedFileName = Sql.ToString(Session["TempFile." + sProcessedFileID]);
                    string sProcessedPathName = Path.Combine(Path.GetTempPath(), sProcessedFileName);
                    if (File.Exists(sProcessedPathName))
                    {
                        var dsProcessed = new DataSet();
                        dsProcessed.ReadXml(sProcessedPathName);
                        if (dsProcessed.Tables.Count == 1) {
                            PreviewGrid(dsProcessed.Tables[0]);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                ctlDynamicButtons.ErrorText = ex.Message;
            }

            LoadImportedList();
        }

        private void LoadImportedList()
        {
            var dbf = DbProviderFactories.GetFactory();
            using (IDbConnection con = dbf.CreateConnection())
            {
                con.Open();
                var cmdSelectImportedList = con.CreateCommand();
                cmdSelectImportedList.CommandType = CommandType.Text;
                cmdSelectImportedList.CommandText = 
@"select * from IMPORTS order by IMPORT_TIME";
                IDbDataAdapter da = dbf.CreateDataAdapter();
                da.SelectCommand = cmdSelectImportedList;
                var ds = new DataSet();
                da.Fill(ds);
                var tblImportedList = ds.Tables[0];
                grdImportedList.DataSource = new DataView(tblImportedList);
                grdImportedList.ApplySort();
                grdImportedList.DataBind();
                con.Close();
            }
        }
        protected void Page_PreRender(object sender, EventArgs e)
        {
            ViewState["xmlMapping"] = xmlMapping.OuterXml;
            if (Sql.ToString(ViewState["xmlSample"]).StartsWith(sImportModule))
                Session[Sql.ToString(ViewState["xmlSample"])] = xml.OuterXml;
        }
        override protected void OnInit(EventArgs e)
        {
            InitializeComponent();
            base.OnInit(e);
        }
        private void InitializeComponent()
        {
            ctlDynamicButtons.Command += new CommandEventHandler(Page_Command);
            this.m_sMODULE = "Import";
            SetMenu(sImportModule);

            string sRelativePath = Sql.ToString(Application["Modules." + sImportModule + ".RelativePath"]);
            if (Sql.IsEmptyString(sRelativePath))
                sRelativePath = "~/" + sImportModule + "/";
            ctlDefaultsView = LoadControl(sRelativePath + "ImportDefaultsView.ascx") as SplendidControl;
            if (ctlDefaultsView != null)
                phDefaultsView.Controls.Add(ctlDefaultsView);
            // 04/29/2008 Paul.  Make use of dynamic buttons. 
            ctlDynamicButtons.AppendButtons(m_sMODULE + ".ImportView", Guid.Empty, Guid.Empty);
        }
        #endregion

    }
}


